﻿Imports System.Data.SqlClient
Public Class clsMantenimientos
    Private mintIdMantenimiento As Integer
    Private mdatFecha As Date
    Private mintIdAutobus As Integer
    Private mstrNoEconomico As String
    Private mintIdRuta As Integer
    Private mstrNombreRuta As String
    Private mintIdOperador As Integer
    Private mstrNombreOperador As String
    Private mintIdMecanico As Integer
    Private mstrNombreMecanico As String
    Private mintIdAutoriza As Integer
    Private mstrNombreAutoriza As String
    Private mstrObservaciones As String
    Private mdblKilometraje As Double
    Private mstrCapturo As String
    Private mintStatus As Integer

    Public Property IdMantenimiento() As Integer
        Get
            Return mintIdMantenimiento
        End Get
        Set(ByVal value As Integer)
            mintIdMantenimiento = value
        End Set
    End Property
    Public Property Fecha As Date
        Get
            Return mdatFecha
        End Get
        Set(ByVal value As Date)
            mdatFecha = value
        End Set
    End Property
    Public Property IdAutobus() As Integer
        Get
            Return mintIdAutobus
        End Get
        Set(ByVal value As Integer)
            mintIdAutobus = value
        End Set
    End Property
    Public Property NoEconomico() As String
        Get
            Return mstrNoEconomico
        End Get
        Set(value As String)
            mstrNoEconomico = value
        End Set
    End Property

    Public Property IdRuta() As Integer
        Get
            Return mintIdRuta
        End Get
        Set(ByVal value As Integer)
            mintIdRuta = value
        End Set
    End Property
    Public Property NombreRuta() As String
        Get
            Return mstrNombreRuta
        End Get
        Set(ByVal value As String)
            mstrNombreRuta = value
        End Set
    End Property
    Public Property IdOperador() As Integer
        Get
            Return mintIdOperador
        End Get
        Set(ByVal value As Integer)
            mintIdOperador = value
        End Set
    End Property
    Public Property NombreOperador() As String
        Get
            Return mstrNombreOperador
        End Get
        Set(ByVal value As String)
            mstrNombreOperador = value
        End Set
    End Property

    Public Property IdMecanico() As Integer
        Get
            Return mintIdMecanico
        End Get
        Set(ByVal value As Integer)
            mintIdMecanico = value
        End Set
    End Property
    Public Property NombreMecanico() As String
        Get
            Return mstrNombreMecanico
        End Get
        Set(ByVal value As String)
            mstrNombreMecanico = value
        End Set
    End Property
    Public Property IdAutoriza() As Integer
        Get
            Return mintIdAutoriza
        End Get
        Set(ByVal value As Integer)
            mintIdAutoriza = value
        End Set
    End Property
    Public Property NombreAutoriza() As String
        Get
            Return mstrNombreAutoriza
        End Get
        Set(ByVal value As String)
            mstrNombreAutoriza = value
        End Set
    End Property
    Public Property Observaciones() As String
        Get
            Return mstrObservaciones
        End Get
        Set(ByVal value As String)
            mstrObservaciones = value
        End Set
    End Property

    Public Property Kilometraje() As Double
        Get
            Return mdblKilometraje
        End Get
        Set(ByVal value As Double)
            mdblKilometraje = value
        End Set
    End Property
    Public Property Capturo() As String
        Get
            Return mstrCapturo
        End Get
        Set(ByVal value As String)
            mstrCapturo = value
        End Set
    End Property
    Public Property Status() As Integer
        Get
            Return mintStatus
        End Get
        Set(ByVal value As Integer)
            mintStatus = value
        End Set
    End Property
    Public Sub New()
        mintIdMantenimiento = 0
        mdatFecha = Now()
        mintIdAutobus = 0
        mstrNoEconomico = ""
        mintIdRuta = 0
        mstrNombreRuta = ""
        mintIdOperador = 0
        mstrNombreOperador = ""
        mintIdMecanico = 0
        mstrNombreMecanico = ""
        mintIdAutoriza = 0
        mstrNombreAutoriza = ""
        mstrObservaciones = ""
        mdblKilometraje = 0
        mstrCapturo = ""
        mintStatus = 0
    End Sub
    Public Sub Buscar()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From Mantenimientos " & _
        " WHERE IdMantenimiento=" & mintIdMantenimiento

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        If rdBuscar.Read() Then
            mdatFecha = rdBuscar("Fecha")
            mintIdAutobus = rdBuscar("IdAutobus")
            mstrNoEconomico = rdBuscar("NoEconomico").ToString.Trim
            mintIdRuta = rdBuscar("IdRuta")
            mstrNombreRuta = rdBuscar("NombreRuta").ToString.Trim
            mintIdOperador = rdBuscar("IdOperador")
            mstrNombreOperador = rdBuscar("NombreOperador").ToString.Trim
            mintIdMecanico = rdBuscar("IdMecanico")
            mstrNombreMecanico = rdBuscar("NombreMecanico").ToString.Trim
            mintIdAutoriza = rdBuscar("IdAutoriza")
            mstrNombreAutoriza = rdBuscar("NombreAutoriza").ToString.Trim
            mstrObservaciones = rdBuscar("Observaciones").ToString.Trim
            mdblKilometraje = rdBuscar("Kilometraje")
            mstrCapturo = rdBuscar("Capturo").ToString.Trim
            mintStatus = rdBuscar("Status")
        Else
            mdatFecha = Now()
            mintIdAutobus = 0
            mstrNoEconomico = ""
            mintIdRuta = 0
            mstrNombreRuta = ""
            mintIdOperador = 0
            mstrNombreOperador = ""
            mintIdMecanico = 0
            mstrNombreMecanico = ""
            mintIdAutoriza = 0
            mstrNombreAutoriza = ""
            mstrObservaciones = ""
            mdblKilometraje = 0
            mstrCapturo = ""
            mintStatus = 0
        End If

        rdBuscar.Close()
        cnConn.Close()


    End Sub
    Public Sub Insertar()
        Dim sql As String = "Insert Into Mantenimientos (" & _
                            "Fecha," & _
                            "IdAutobus," & _
                            "NoEconomico," & _
                            "IdRuta," & _
                            "NombreRuta," & _
                            "IdOperador," & _
                            "NombreOperador," & _
                            "IdMecanico," & _
                            "NombreMecanico," & _
                            "IdAutoriza," & _
                            "NombreAutoriza," & _
                            "Observaciones," & _
                            "Kilometraje," & _
                            "Capturo, " & _
                            "Status)" & _
                            "VALUES (" & _
                            "@Fecha," & _
                            "@IdAutobus," & _
                            "substring(@NoEconomico,1,50)," & _
                            "@IdRuta," & _
                            "substring(@NombreRuta,1,100)," & _
                            "@IdOperador," & _
                            "substring(@NombreOperador,1,200)," & _
                            "@IdMecanico," & _
                            "substring(@NombreMecanico,1,200)," & _
                            "@IdAutoriza," & _
                            "substring(@NombreAutoriza,1,200)," & _
                            "substring(@Observaciones,1,500)," & _
                            "@Kilometraje," & _
                            "substring(@Capturo,1,50), " & _
                            "@Status)" & _
                            " SELECT SCOPE_IDENTITY()"


        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim Command As New SqlCommand(sql, cnConn)

        Command.Parameters.AddWithValue("Fecha", mdatFecha)
        Command.Parameters.AddWithValue("IdAutobus", mintIdAutobus)
        Command.Parameters.AddWithValue("NoEconomico", mstrNoEconomico)
        Command.Parameters.AddWithValue("IdRuta", mintIdRuta)
        Command.Parameters.AddWithValue("NombreRuta", mstrNombreRuta)
        Command.Parameters.AddWithValue("IdOperador", mintIdOperador)
        Command.Parameters.AddWithValue("NombreOperador", mstrNombreOperador)
        Command.Parameters.AddWithValue("IdMecanico", mintIdMecanico)
        Command.Parameters.AddWithValue("NombreMecanico", mstrNombreMecanico)
        Command.Parameters.AddWithValue("IdAutoriza", mintIdAutoriza)
        Command.Parameters.AddWithValue("NombreAutoriza", mstrNombreAutoriza)
        Command.Parameters.AddWithValue("Observaciones", mstrObservaciones)
        Command.Parameters.AddWithValue("Kilometraje", mdblKilometraje)
        Command.Parameters.AddWithValue("Capturo", mstrCapturo)
        Command.Parameters.AddWithValue("Status", mintStatus)

        cnConn.Open()
        'Dim RowsAffected As Integer = Command.ExecuteNonQuery
        IdMantenimiento = Convert.ToInt32(Command.ExecuteScalar)

        If IdMantenimiento > 0 Then
            cnConn.Close()
        Else
            MsgBox("No fue posible insertar el mantenimiento ", MsgBoxStyle.Exclamation, "Aviso")
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End If
    End Sub
    Public Sub Actualizar()

        Dim strSqlQry As String
        Dim cnConn As New SqlConnection

        cnConn.ConnectionString = CitraConnection
        strSqlQry = "Update Mantenimientos Set " & _
                    "Fecha=@Fecha," & _
                    "IdAutobus=@IdAutobus," & _
                    "NoEconomico=substring(@NoEconomico,1,50)," & _
                    "IdRuta=@IdRuta," & _
                    "NombreRuta=substring(@NombreRuta,1,100)," & _
                    "IdOperador=@IdOperador," & _
                    "NombreOperador=substring(@NombreOperador,1,200)," & _
                    "IdMecanico=@IdMecanico," & _
                    "NombreMecanico=substring(@NombreMecanico,1,200)," & _
                    "IdAutoriza=@IdAutoriza," & _
                    "NombreAutoriza=substring(@NombreAutoriza,1,200)," & _
                    "Observaciones=substring(@Observaciones,1,500)," & _
                    "Kilometraje=@Kilometraje," & _
                    "Capturo=substring(@Capturo,1,50) " & _
                    "Where IdMecanico=@IdMecanico"


        Dim Command As New SqlCommand(strSqlQry, cnConn)
        Command.Parameters.AddWithValue("IdMantenimiento", mintIdMantenimiento)
        Command.Parameters.AddWithValue("Fecha", mdatFecha)
        Command.Parameters.AddWithValue("IdAutobus", mintIdAutobus)
        Command.Parameters.AddWithValue("NoEconomico", mstrNoEconomico)
        Command.Parameters.AddWithValue("IdRuta", mintIdRuta)
        Command.Parameters.AddWithValue("NombreRuta", mstrNombreRuta)
        Command.Parameters.AddWithValue("IdOperador", mintIdOperador)
        Command.Parameters.AddWithValue("NombreOperador", mstrNombreOperador)
        Command.Parameters.AddWithValue("IdMecanico", mintIdMecanico)
        Command.Parameters.AddWithValue("NombreMecanico", mstrNombreMecanico)
        Command.Parameters.AddWithValue("IdAutoriza", mintIdAutoriza)
        Command.Parameters.AddWithValue("NombreAutoriza", mstrNombreAutoriza)
        Command.Parameters.AddWithValue("Observaciones", mstrObservaciones)
        Command.Parameters.AddWithValue("Kilometraje", mdblKilometraje)
        Command.Parameters.AddWithValue("Capturo", mstrCapturo)
        cnConn.Open()

        Dim RowsAffected As Integer = Command.ExecuteNonQuery

        If RowsAffected > 0 Then
            cnConn.Close()
        Else
            MsgBox("No fue posible actualizar el mantenimiento ", MsgBoxStyle.Exclamation, "Aviso")
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End If



    End Sub
End Class
